﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;

namespace Matrix.MaKaYunShang.DAL
{
    public partial class CouponDAL : BaseDAL<CouponDAL>
    {
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Coupon model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into Coupon(");
            strSql.Append("CouponName,CouponCategoryId,TotalQuantity,SendQuantity,ReceiveQuantity,Description,Subtitle,Title,StartTime,EndTime,Brand,LeastCost,ReduceCost,DiscountValue,CardValue,Instruction,Disclaimer,IsPlatform,IsLimitBrand,IsLimitCommodity,IsLimitCommodityCategory,IsMultiUse,AddTime,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@CouponName,@CouponCategoryId,@TotalQuantity,@SendQuantity,@ReceiveQuantity,@Description,@Subtitle,@Title,@StartTime,@EndTime,@Brand,@LeastCost,@ReduceCost,@DiscountValue,@CardValue,@Instruction,@Disclaimer,@IsPlatform,@IsLimitBrand,@IsLimitCommodity,@IsLimitCommodityCategory,@IsMultiUse,@AddTime,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@CouponName", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@CouponCategoryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@TotalQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@Subtitle", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@Title", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@StartTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@EndTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Brand", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@LeastCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@ReduceCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@DiscountValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CardValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Instruction", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@Disclaimer", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@IsPlatform", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLimitBrand", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLimitCommodity", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLimitCommodityCategory", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsMultiUse", SqlDbType.Bit,1) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.CouponName;
            parameters[1].Value = model.CouponCategoryId;
            parameters[2].Value = model.TotalQuantity;
            parameters[3].Value = model.SendQuantity;
            parameters[4].Value = model.ReceiveQuantity;
            parameters[5].Value = model.Description;
            parameters[6].Value = model.Subtitle;
            parameters[7].Value = model.Title;
            parameters[8].Value = model.StartTime;
            parameters[9].Value = model.EndTime;
            parameters[10].Value = model.Brand;
            parameters[11].Value = model.LeastCost;
            parameters[12].Value = model.ReduceCost;
            parameters[13].Value = model.DiscountValue;
            parameters[14].Value = model.CardValue;
            parameters[15].Value = model.Instruction;
            parameters[16].Value = model.Disclaimer;
            parameters[17].Value = model.IsPlatform;
            parameters[18].Value = model.IsLimitBrand;
            parameters[19].Value = model.IsLimitCommodity;
            parameters[20].Value = model.IsLimitCommodityCategory;
            parameters[21].Value = model.IsMultiUse;
            parameters[22].Value = model.AddTime;
            parameters[23].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Coupon model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Coupon set ");

            strSql.Append(" CouponName = @CouponName , ");
            strSql.Append(" CouponCategoryId = @CouponCategoryId , ");
            strSql.Append(" TotalQuantity = @TotalQuantity , ");
            strSql.Append(" SendQuantity = @SendQuantity , ");
            strSql.Append(" ReceiveQuantity = @ReceiveQuantity , ");
            strSql.Append(" Description = @Description , ");
            strSql.Append(" Subtitle = @Subtitle , ");
            strSql.Append(" Title = @Title , ");
            strSql.Append(" StartTime = @StartTime , ");
            strSql.Append(" EndTime = @EndTime , ");
            strSql.Append(" Brand = @Brand , ");
            strSql.Append(" LeastCost = @LeastCost , ");
            strSql.Append(" ReduceCost = @ReduceCost , ");
            strSql.Append(" DiscountValue = @DiscountValue , ");
            strSql.Append(" CardValue = @CardValue , ");
            strSql.Append(" Instruction = @Instruction , ");
            strSql.Append(" Disclaimer = @Disclaimer , ");
            strSql.Append(" IsPlatform = @IsPlatform , ");
            strSql.Append(" IsLimitBrand = @IsLimitBrand , ");
            strSql.Append(" IsLimitCommodity = @IsLimitCommodity , ");
            strSql.Append(" IsLimitCommodityCategory = @IsLimitCommodityCategory , ");
            strSql.Append(" IsMultiUse = @IsMultiUse , ");
            strSql.Append(" AddTime = @AddTime , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@CouponName", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@CouponCategoryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@TotalQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@Subtitle", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@Title", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@StartTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@EndTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Brand", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@LeastCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@ReduceCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@DiscountValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CardValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@Instruction", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@Disclaimer", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@IsPlatform", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLimitBrand", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLimitCommodity", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsLimitCommodityCategory", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsMultiUse", SqlDbType.Bit,1) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.CouponName;
            parameters[2].Value = model.CouponCategoryId;
            parameters[3].Value = model.TotalQuantity;
            parameters[4].Value = model.SendQuantity;
            parameters[5].Value = model.ReceiveQuantity;
            parameters[6].Value = model.Description;
            parameters[7].Value = model.Subtitle;
            parameters[8].Value = model.Title;
            parameters[9].Value = model.StartTime;
            parameters[10].Value = model.EndTime;
            parameters[11].Value = model.Brand;
            parameters[12].Value = model.LeastCost;
            parameters[13].Value = model.ReduceCost;
            parameters[14].Value = model.DiscountValue;
            parameters[15].Value = model.CardValue;
            parameters[16].Value = model.Instruction;
            parameters[17].Value = model.Disclaimer;
            parameters[18].Value = model.IsPlatform;
            parameters[19].Value = model.IsLimitBrand;
            parameters[20].Value = model.IsLimitCommodity;
            parameters[21].Value = model.IsLimitCommodityCategory;
            parameters[22].Value = model.IsMultiUse;
            parameters[23].Value = model.AddTime;
            parameters[24].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Coupon set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Coupon set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, CouponName, CouponCategoryId, TotalQuantity, SendQuantity, ReceiveQuantity, Description, Subtitle, Title, StartTime, EndTime, Brand, LeastCost, ReduceCost, DiscountValue, CardValue, Instruction, Disclaimer, IsPlatform, IsLimitBrand, IsLimitCommodity, IsLimitCommodityCategory, IsMultiUse, AddTime, IsDelete  ");
            strSql.Append("  from Coupon ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Coupon where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法

        /// <summary>
        /// 获取用户优惠券列表
        /// </summary>
        /// <param name="consumerId">消费者ID</param>
        /// <param name="isChooseUsable">是否只提取可用优惠券</param>
        /// <returns></returns>
        public List<Coupon> GetCouponListOfCousumer(int consumerId, bool isChooseUsable)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT cp.Id, cp.CouponName, cp.CouponCategoryId, cp.TotalQuantity, cp.SendQuantity, cp.ReceiveQuantity, ");
            sbSql.Append("cp.Description, cp.Subtitle, cp.Title, cp.StartTime, cp.EndTime, cp.Brand, cp.LeastCost, cp.ReduceCost, ");
            sbSql.Append("cp.DiscountValue, cp.CardValue, cp.Instruction, cp.Disclaimer, cp.IsPlatform, cp.IsLimitBrand, ");
            sbSql.Append("cp.IsLimitCommodity, cp.IsLimitCommodityCategory, cp.IsMultiUse, cp.AddTime, cp.IsDelete, cat.CategoryName AS CouponCategoryName, ");
            sbSql.Append("cc.Id AS ConsumerCouponId, cat.CategoryCode ");
            sbSql.Append("FROM Coupon cp ");
            sbSql.Append("INNER JOIN ConsumerCoupon cc ON cc.CouponId=cp.Id ");
            sbSql.Append("INNER JOIN CouponCategory cat ON cat.Id=cp.CouponCategoryId ");
            sbSql.Append("WHERE cp.IsDelete=0 AND cc.IsDelete=0 AND cat.IsDelete=0 AND cc.ConsumerId=@ConsumerId ");

            if (isChooseUsable)
            {
                sbSql.Append("AND cp.StartTime<=getdate() AND cp.EndTime>=getdate() ");
            }

            sbSql.Append(" ORDER BY cp.EndTime DESC, cp.Id DESC");
            SqlParameter[] parameters = {
					new SqlParameter("@ConsumerId", SqlDbType.Int,4)
			};
            parameters[0].Value = consumerId;

            var lsCoupon = DbHelperSQL.ExecuteSqlList<Coupon>(sbSql.ToString(), parameters);
            if (lsCoupon != null && lsCoupon.Count > 0)
            {
                return lsCoupon.ToList();
            }
            return null;

        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <returns></returns>
        public List<ViewCouponDto> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder, ref int totalCount)
        {
            string strWhere = " IsDelete = 0 ";

            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere += " AND (CouponName LIKE '%" + searchKeyWord + "%' OR TotalQuantity LIKE '%" + searchKeyWord +
                            "%' OR SendQuantity LIKE '%" + searchKeyWord + "%' OR CardValue LIKE '%" + searchKeyWord +
                            "%' OR StartTime LIKE '%" + searchKeyWord + "%' OR EndTime LIKE '%" + searchKeyWord + "%') ";
            }

            return GetSimpleListPage<ViewCouponDto>(pageIndex, pageSize, "Coupon", null, null, colOrder, strWhere,
                ref totalCount);
        }

        /// <summary>
        /// 获取用户优惠券信息
        /// </summary>
        /// <param name="consumerCouponId">消费者优惠券ID</param>
        /// <returns></returns>
        public Coupon GetCouponInfoByConsumerCouponId(int consumerCouponId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT cp.Id, cp.CouponName, cp.CouponCategoryId, cp.TotalQuantity, cp.SendQuantity, cp.ReceiveQuantity, ");
            sbSql.Append("cp.Description, cp.Subtitle, cp.Title, cp.StartTime, cp.EndTime, cp.Brand, cp.LeastCost, cp.ReduceCost, ");
            sbSql.Append("cp.DiscountValue, cp.CardValue, cp.Instruction, cp.Disclaimer, cp.IsPlatform, cp.IsLimitBrand, ");
            sbSql.Append("cp.IsLimitCommodity, cp.IsLimitCommodityCategory, cp.IsMultiUse, cp.AddTime, cp.IsDelete, cat.CategoryName AS CouponCategoryName, cat.CategoryCode ");
            sbSql.Append("FROM Coupon cp ");
            sbSql.Append("INNER JOIN ConsumerCoupon cc ON cc.CouponId=cp.Id ");
            sbSql.Append("INNER JOIN CouponCategory cat ON cat.Id=cp.CouponCategoryId ");
            sbSql.Append("WHERE cp.IsDelete=0 AND cc.IsDelete=0 AND cat.IsDelete=0 AND cc.Id=@ConsumerCouponId ");
            sbSql.Append(" ORDER BY cp.EndTime ASC, cp.Id ASC");
            SqlParameter[] parameters = {
					new SqlParameter("@ConsumerCouponId", SqlDbType.Int,4),
                    new SqlParameter("@CouponId", SqlDbType.Int,4)
			};
            parameters[0].Value = consumerCouponId;

            var lsCoupon = DbHelperSQL.ExecuteSqlList<Coupon>(sbSql.ToString(), parameters);
            if (lsCoupon != null && lsCoupon.Count > 0)
            {
                return lsCoupon[0];
            }
            return null;

        }

        /// <summary>
        /// 获取用户优惠券信息
        /// </summary>
        /// <param name="consumerCouponIds">消费者优惠券ID集合</param>
        /// <returns></returns>
        public List<Coupon> BatchGetCouponInfoByConsumerCouponIds(string consumerCouponIds)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT cp.Id, cp.CouponName, cp.CouponCategoryId, cp.TotalQuantity, cp.SendQuantity, cp.ReceiveQuantity, ");
            sbSql.Append("cp.Description, cp.Subtitle, cp.Title, cp.StartTime, cp.EndTime, cp.Brand, cp.LeastCost, cp.ReduceCost, ");
            sbSql.Append("cp.DiscountValue, cp.CardValue, cp.Instruction, cp.Disclaimer, cp.IsPlatform, cp.IsLimitBrand, ");
            sbSql.Append("cp.IsLimitCommodity, cp.IsLimitCommodityCategory, cp.IsMultiUse, cp.AddTime, cp.IsDelete, cat.CategoryName AS CouponCategoryName, ");
            sbSql.Append("cc.Id AS ConsumerCouponId ");
            sbSql.Append("FROM Coupon cp ");
            sbSql.Append("INNER JOIN ConsumerCoupon cc ON cc.CouponId=cp.Id ");
            sbSql.Append("INNER JOIN CouponCategory cat ON cat.Id=cp.CouponCategoryId ");
            sbSql.Append("WHERE cp.IsDelete=0 AND cc.IsDelete=0 AND cat.IsDelete=0 AND cc.Id IN (" + consumerCouponIds + ") ");
            sbSql.Append(" ORDER BY cp.EndTime ASC, cp.Id ASC");

            var lsCoupon = DbHelperSQL.ExecuteSqlList<Coupon>(sbSql.ToString());

            return lsCoupon == null || lsCoupon.Count <= 0 ? null : lsCoupon.ToList();
        }

        /// <summary>
        /// 获得排除制定类别数据列表
        /// </summary>
        public List<Coupon> GetListExceptSingleCategoryCode(string categoryCodes)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT cp.* ");
            sbSql.Append("FROM Coupon cp ");
            sbSql.Append("INNER JOIN CouponCategory cat ON cat.Id=cp.CouponCategoryId ");
            sbSql.Append("WHERE cp.IsDelete=0  AND cat.IsDelete=0 ");

            if (!string.IsNullOrWhiteSpace(categoryCodes))
            {
                sbSql.Append("AND cat.CategoryCode NOT IN (" + categoryCodes + ") ");
            }

            var lsRet = DbHelperSQL.ExecuteSqlList<Coupon>(sbSql.ToString());
            return lsRet == null || lsRet.Count == 0 ? null : lsRet.ToList();
        }

        /// <summary>
        /// 根据条件获取优惠券总数
        /// </summary>
        /// <remarks>2016-08-22 万乐园 创建</remarks>
        public int GetTotalCount(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Coupon where IsDelete=0");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return int.Parse(DbHelperSQL.GetSingle(strSql.ToString()).ToString());
        }

        public DataSet GetListByCouponName(string couponName)
        {
            var strSql = new StringBuilder();
            strSql.Append("select c.*,cc.CategoryName as CouponCategoryName ");
            strSql.Append(" FROM Coupon c left join CouponCategory cc on cc.Id=c.CouponCategoryId  where c.IsDelete=0 and cc.IsDelete=0");
            if (!string.IsNullOrEmpty(couponName))
            {
                strSql.Append(string.Format(" and  c.CouponName like '%{0}%' ", couponName));
            }

            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
    }
}
